# -*- coding: utf-8 -*-
import datetime
import json
import smtplib
import time

import requests
import xlwt
import logging

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import pymysql

class Pricing(object):
    '''在本地生成最低价excel'''

    def __init__(self):

        self.title = 'ota_account'
        self.dingding_url = "https://oapi.dingtalk.com/robot/send?access_token=8f883e4b2723b1eefcb34fa73cbf6cebe47b3aa605e439216e04e73220b21134"
        self.batch_no = time.strftime('%Y%m%d%H', time.localtime(time.time()))
        HOST = 'oyo-scrapy-database.mysql.rds.aliyuncs.com'
        PORT = 3306
        USER = 'scrapy'
        PASSWD = 'Podf&T$2'
        DB = 'spider'
        CHARSET = 'utf8mb4'

        self.conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWD, db=DB, charset=CHARSET)
        self.cur = self.conn.cursor()



    def send_dingding(self, batch_no,ota_channel_en):
        dic = self.msg_data(batch_no,ota_channel_en)
        msg_body = []
        # 平台分类
        if ota_channel_en=='elong':
            for k, v in dic.items():
                if k == 0:
                    k = '正确'
                if k == 1:
                    k = '账号或密码错误'
                if k == 2:
                    k = '需要强制更新密码'
                msg_body.append("%s：%s \n\n" % (k, v))

        elif ota_channel_en=='qunar':
            for k, v in dic.items():
                if k == 0:
                    k = '正确'
                if k == 1:
                    k = '账号或密码错误'
                if k == 2:
                    k = '正确----手机验证码登录'
                if k == 3:
                    k = '仅支持手机/邮箱/用户名登录'
                msg_body.append("%s：%s \n\n" % (k, v))

        elif ota_channel_en=='ctrip':
            for k, v in dic.items():
                if k == 0:
                    k = '正确'
                if k == 1:
                    k = '账号或密码错误'
                if k == 2:
                    k = '正确—请关注合作协议更新'
                msg_body.append("%s：%s \n\n" % (k, v))

        elif ota_channel_en=='meituan':
            for k, v in dic.items():
                if k == 0:
                    k = '正确'
                if k == 1:
                    k = '账号或密码错误'
                if k == 2:
                    k = '英文验证码'
                if k == 3:
                    k = '短信验证'
                if k == 5:
                    k = '密码过于简单，账号锁定'
                msg_body.append("%s：%s \n\n" % (k, v))


        head_msg = " #### %s账号验证%s \n\n  ———————————————— \n\n " % (ota_channel_en,batch_no)
        head_msg += "\n\n".join(msg_body)
        post_data = {'msgtype': 'markdown',
                     'markdown': {'title': self.title,
                                  'text': head_msg
                                  }
                     }
        headers = {'Content-Type': 'application/json; charset=utf-8'}
        requests.post(url=self.dingding_url, data=json.dumps(post_data), headers=headers)

    def msg_data(self, batch_no,ota_channel_en):
        sql1 = """SELECT
                    s.%s_Status,
                    COUNT( 1 ) 
                FROM
                    ota_back_account_info i
                    LEFT JOIN ota_back_account_status s ON i.CRS_ID = s.CRS_ID 
                WHERE
                    i.ota_channel_en = '%s' 
                    AND s.batch_no = %s
                    GROUP BY s.%s_Status
                    ORDER BY s.%s_Status""" % (ota_channel_en,ota_channel_en,batch_no,ota_channel_en,ota_channel_en)
        self.cur.execute(sql1)
        d1 = self.cur.fetchall()
        dic = {}
        for data in d1:
            dic[data[0]] = data[1]

        sql2 = """SELECT
            MAX( i.verify_time ),
            MIN( i.verify_time ),
            COUNT( 1 ) 
        FROM
            ota_back_account_info i
            LEFT JOIN ota_back_account_status s ON i.CRS_ID = s.CRS_ID 
        WHERE
            i.ota_channel_en = '%s' 
            AND s.batch_no = %s""" % (ota_channel_en,batch_no)
        self.cur.execute(sql2)
        d2 = self.cur.fetchall()
        for data in d2:
            start = str(data[1])
            end = str(data[0])
            try:
                total_time = str(data[0] - data[1])
            except:
                total_time = 0
            num = data[2]
            dic['开始时间'] = start
            dic['结束时间'] = end
            dic['验证耗时'] = total_time
            dic['验证完成账号总数'] = num

        sql3 = """  SELECT
                        * 
                    FROM
                        ota_back_account_info 
                    WHERE
                        ota_channel_en = '%s' 
                        AND batch_no = '%s'""" % (ota_channel_en,batch_no)
        self.cur.execute(sql3)
        d3 = self.cur.fetchall()
        repeat_num = len(d3)
        dic['需验证账号总数'] = repeat_num

        # sql4 = """SELECT
        #             DISTINCT collect_host
        #         FROM
        #             ota_back_account_info i
        #             LEFT JOIN ota_back_account_status s ON i.CRS_ID = s.CRS_ID
        #         WHERE
        #             i.ota_channel_en = 'Meituan'
        #             AND s.batch_no = '2019011715'
        #             AND s.collect_host is not null"""
        # cur.execute(sql4)
        # d4 = cur.fetchall()
        # host_num = len(d4)
        # dic['主机数量'] = host_num
        return dic

if __name__ == "__main__":

    now_time = datetime.datetime.now()
    yesterday = now_time + datetime.timedelta(days=-1)
    batch_no = yesterday.strftime('%Y%m%d')+'21'

    # batch_no = time.strftime('%Y%m%d%H', time.localtime(time.time()))
    ota_channel_ens=['ctrip','meituan','qunar','elong']
    for ota_channel_en in ota_channel_ens:
        d = Pricing()
        d.send_dingding(batch_no,ota_channel_en)
